﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.OracleClient;
using Pms.Utils;
using Biblioteca.Utils;

namespace SERIX.Classes
{
    public class CAcaoIndicador
    {

        public static DataTable Obter(string IDT_PROG, string IDT_ACAO, string ANO_AIS, string IDT_CLA_IND, string login)
        {
            if (string.IsNullOrEmpty(IDT_PROG) || string.IsNullOrEmpty(IDT_ACAO) || string.IsNullOrEmpty(ANO_AIS) || string.IsNullOrEmpty(IDT_CLA_IND))
                return new DataTable();

            OracleCommand command = new OracleCommand();

            string SQL = @"
select ind.idt_ind, dsc_IND
      ,CAST(
        (case when idt_ACAO  is null  then 0 else 1
        end ) as int ) as ACAO
    ,idt_ACAO
    from Serix.SRX_INDICADORES ind
  left join Serix.SRX_ACOES_INDICADORES ais on ind.IDT_IND = ais.IDT_IND and IDT_ACAO = :IDT_ACAO and ais.ANO_AIS = :ANO_AIS and ais.IDT_PROG = :IDT_PROG
  where (:IDT_CLA_IND is null or ind.idt_cla_ind = :IDT_CLA_IND)

"; 
            OracleConnection cnn = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, false);
            try
            {
                
                command.Connection = cnn;
                command.CommandType = CommandType.Text;
                command.CommandText = SQL;
                command.Parameters.Add(new OracleParameter("IDT_CLA_IND", OracleType.Number));
                command.Parameters.Add(new OracleParameter("IDT_PROG", OracleType.Number));
                command.Parameters.Add(new OracleParameter("IDT_ACAO", OracleType.Number));
                command.Parameters.Add(new OracleParameter("ANO_AIS", OracleType.Number));
                command.Parameters["IDT_CLA_IND"].Value = IDT_CLA_IND;
                command.Parameters["IDT_PROG"].Value = IDT_PROG;
                command.Parameters["IDT_ACAO"].Value = IDT_ACAO;
                command.Parameters["ANO_AIS"].Value = ANO_AIS;
                DataTable tbl = new DataTable();
                tbl.Load(command.ExecuteReader(CommandBehavior.CloseConnection));
                return tbl;
            }
            catch (Exception e)
            {
                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }
            finally
            {
                cnn.Close();
                command.Dispose();
                if (command != null && command.Connection.State == ConnectionState.Open)
                    command.Connection.Close();
            }
        }

        /// <summary>
        /// Alterando um Programas_municipios.
        /// </summary>
        public static void RegistroEmLote(int idt_Prog, int idt_acao, List<int> idts_Ind, List<int> idts_IndExcluir, int ANO_AIS, string Login)
        {
            OracleConnection cnn = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, Login, BiblioPMS.Demo);
            OracleCommand command = new OracleCommand();
            OracleTransaction trans;

            string SQL_DELETE = @"DELETE FROM Serix.SRX_ACOES_INDICADORES
                                   WHERE idt_ACAO = :idt_ACAO and ANO_AIS = :ANO_AIS and idt_prog = :IDT_PROG and idt_ind = :idt_ind";

            string SQL_INSERT = @"INSERT INTO Serix.SRX_ACOES_INDICADORES
                                   (idt_ACAO, idt_ind, ANO_AIS, IDT_PROG, IDT_AIS)
                                    VALUES (:idt_ACAO, :idt_ind, :ANO_AIS, :IDT_PROG,
                                    (select nvl(max(IDT_AIS),0) from Serix.SRX_ACOES_INDICADORES)+1)";

            try
            {
                command.Connection = cnn;
                trans = command.Connection.BeginTransaction();
                command.Transaction = trans;
                command.CommandType = CommandType.Text;
                command.CommandText = SQL_DELETE;

                command.Parameters.Add(new OracleParameter("idt_acao", OracleType.Number));
                command.Parameters.Add(new OracleParameter("ANO_AIS", OracleType.Number));
                command.Parameters.Add(new OracleParameter("IDT_PROG", OracleType.Number));
                command.Parameters["idt_acao"].Value = idt_acao;
                command.Parameters["ANO_AIS"].Value = ANO_AIS;
                command.Parameters["IDT_PROG"].Value = idt_Prog;

                command.Parameters.Add(new OracleParameter("idt_ind", OracleType.Number));
                foreach (int idt_ind in idts_IndExcluir)
                {
                    command.Parameters["idt_ind"].Value = idt_ind;
                    command.ExecuteNonQuery();
                }

                //command.ExecuteNonQuery();
                command.Parameters.Clear();

                command.CommandText = SQL_INSERT;

                command.Parameters.Add(new OracleParameter("idt_acao", OracleType.Number));
                command.Parameters.Add(new OracleParameter("IDT_PROG", OracleType.Number));
                command.Parameters.Add(new OracleParameter("ANO_AIS", OracleType.Number));
                command.Parameters["IDT_PROG"].Value = idt_Prog;
                command.Parameters["idt_acao"].Value = idt_acao;
                command.Parameters["ANO_AIS"].Value = ANO_AIS;

                command.Parameters.Add(new OracleParameter("idt_ind", OracleType.Number));
                foreach (int idt_ind in idts_Ind)
                {
                    command.Parameters["idt_ind"].Value = idt_ind;
                    command.ExecuteNonQuery();
                }

                command.Transaction.Commit();
            }
            catch (Exception e)
            {
                command.Transaction.Rollback();

                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }
            finally
            {
                cnn.Close();
                command.Dispose();
                if (command.Connection != null && command.Connection.State == ConnectionState.Open)
                    command.Connection.Close();
            }
        }

    }
}
